package com.xqm.poi.excel;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileOutputStream;
import java.io.IOException;

/**
 * @author ShiMing
 * @version 1.0
 * @name ExcelTest
 * @description TODO
 * @date 2020/8/23
 */

public class ExcelWriteTest {

    final String PATH = "G:\\Java\\IDEA\\xqm-poi\\src\\main\\java\\com\\xqm\\poi\\";

    @Test
    public void writeTest03() throws IOException {
        // 1. 创建一个工作薄 excel03:HSSFWorkbook
        Workbook workbook = new HSSFWorkbook();
        // 2. 创建一个工作表
        Sheet sheet = workbook.createSheet("测试表1");
        // 3. 创建一行
        // 第一行
        Row row1 = sheet.createRow(0);
        // 4. 创建一个单元格（1，1）
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("1,1");
        // (1,2)
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("1,2");

        // 第二行
        Row row2 = sheet.createRow(1);
        // （2，1）
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("2,1：时间测试");
        // （2，2）
        Cell cell22 = row2.createCell(1);
        String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue("2,2：" + time);

        // 生成表
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "excel03.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        System.out.println("excel03生成完毕");
    }

    @Test
    public void writeTest07() throws IOException {
        // 1. 创建一个工作薄 excel07:XSSFWorkbook
        Workbook workbook = new XSSFWorkbook();
        // 2. 创建一个工作表
        Sheet sheet = workbook.createSheet("测试表1");
        // 3. 创建一行
        // 第一行
        Row row1 = sheet.createRow(0);
        // 4. 创建一个单元格（1，1）
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("1,1");
        // (1,2)
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("1,2");

        // 第二行
        Row row2 = sheet.createRow(1);
        // （2，1）
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("2,1：时间测试");
        // （2，2）
        Cell cell22 = row2.createCell(1);
        String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue("2,2：" + time);

        // 生成表
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "excel07.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        System.out.println("excel07生成完毕");
    }

    @Test
    public void writeBigData03() throws IOException {
        // 时间
        long begin = System.currentTimeMillis();

        Workbook workbook = new HSSFWorkbook();

        Sheet sheet = workbook.createSheet();

        for (int rowNum = 0; rowNum < 65536; rowNum++) {
        //for (int rowNum = 0; rowNum < 65538; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 1; cellNum < 11; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        System.out.println("over");
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "BigData03.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();

        long end = System.currentTimeMillis();

        System.out.println((double) (end - begin) / 1000);
    }

    @Test
    public void writeBigData07() throws IOException {
        // 时间
        long begin = System.currentTimeMillis();

        Workbook workbook = new XSSFWorkbook();

        Sheet sheet = workbook.createSheet();

        for (int rowNum = 1; rowNum < 65537; rowNum++) {
        //for (int rowNum = 1; rowNum < 100001; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 1; cellNum < 11; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        System.out.println("over");
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "BigData07.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();

        long end = System.currentTimeMillis();

        System.out.println((double) (end - begin) / 1000);
    }

    @Test
    public void writeBigData07S() throws IOException {
        // 时间
        long begin = System.currentTimeMillis();

        Workbook workbook = new SXSSFWorkbook();

        Sheet sheet = workbook.createSheet();

        //for (int rowNum = 1; rowNum < 65537; rowNum++) {
        for (int rowNum = 1; rowNum < 100001; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 1; cellNum < 11; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        System.out.println("over");
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "BigData07S.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();

        // 清除临时文件
        ((SXSSFWorkbook)workbook).dispose();
        long end = System.currentTimeMillis();

        System.out.println((double) (end - begin) / 1000);
    }

}
